import requests
from lxml import etree
import pymysql
import os


# 定义数据库链接信息
class ConnectMysql:
    host = "localhost"
    user = "root"
    password = "000000"


# 定义房屋信息
class HouseDetail:

    def __init__(self,
                 # 标题
                 tittle,
                 # 价格 ？/月
                 price,
                 # 支付方式：季付、年付。。
                 payment,
                 # 出租方式：整租、合租。。。
                 leasingMode,
                 # 户型：2室1厅。。。
                 houseType,
                 # 面积
                 area,
                 # 朝向
                 orientation,
                 # 楼层
                 floor,
                 # 装修
                 decoration,
                 # 小区
                 housingEstate,
                 # 地址
                 address,
                 # 亮点
                 brightSpot,
                 # 小区介绍
                 housingEstateIntroduction):
        self.__tittle = tittle
        self.__price = price
        self.__payment = payment
        self.__leasingMode = leasingMode
        self.__houseType = houseType
        self.__area = area
        self.__orientation = orientation
        self.__floor = floor
        self.__decoration = decoration
        self.__housingEstate = housingEstate
        self.__address = address
        self.__brightSpot = brightSpot
        self.__housingEstateIntroduction = housingEstateIntroduction


# 返回一页的所有房屋信息详情页的URL
def getOnePageHouseList(url: str) -> list:
    # 定义返回值：一页中的所有详情页的url
    result = []
    # 请求头
    headers = {
        "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.0.0 Safari/537.36"
    }
    # 请求页面html
    html = requests.get(url=url, headers=headers).text
    tree = etree.HTML(html)
    # 房源列表
    houseList = tree.xpath('/html/body/div[5]/div[5]/div[1]/div[3]/dl')
    for house in houseList:
        house_href = house.xpath('./dt/a/@href')
        house_href = 'https://taiyuan.zu.fang.com' + house_href[0] + '?rfss=1-45cf0fa0289599bf97-0a'
        result.append(house_href)
    # 未爬取到数据的提示新信息
    if len(result) == 0:
        print(url + '未采集到数据。')
    # 返回结果集
    return result


# 根据房屋详情页的URL解析每一个房屋信息，返回HouseDetail对象
def getOneHouseDetail(url: str) -> HouseDetail:
    # 请求头
    headers = {
        "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.0.0 Safari/537.36"
    }
    # 请求页面html
    html = requests.get(url=url, headers=headers).text
    tree = etree.HTML(html)

    # 解析数据
    try:
        tittle = tree.xpath("//div[@class='tab-cont clearfix']/div[@class='title']/text()")[0].lstrip().rstrip()
    except:
        tittle = 'null'

    try:
        price = tree.xpath("//div[@class='trl-item sty1']/i/text()")[0]
    except:
        price = 'null'

    try:
        payment = tree.xpath("//div[@class='trl-item sty1']/a[@class='link_rk']/text()")[0]
    except:
        payment = 'null'

    try:
        leasingMode = tree.xpath(
            "//div[@class='trl-item1 w130']/div[@class='tt']/a[@class='link_rk']/text() | "
            "//div[@class='trl-item1 w146']/div[@class='tt']/a[@class='link_rk']/text()")[0]
    except:
        leasingMode = 'null'

    try:
        houseType = tree.xpath(
            "//div[@class='tr-line clearfix'][1]/div[@class='trl-item1 w162']/div[@class='tt']/text() | "
            "//div[@class='tr-line clearfix'][1]/div[@class='trl-item1 w182']/div[@class='tt']/text()")[0]
    except:
        houseType = 'null'

    try:
        area = tree.xpath(
            "//div[@class='trl-item1 w100']/div[@class='tt']/text() | "
            "//div[@class='tr-line clearfix'][1]/div[@class='trl-item1 w132']/div[@class='tt']/text()")[0]
    except:
        area = 'null'

    try:
        orientation = tree.xpath(
            "//div[@class='tr-line clearfix'][2]/div[@class='trl-item1 w130']/div[@class='tt']/text() | "
            "//div[@class='tr-line clearfix'][2]/div[@class='trl-item1 w146']/div[@class='tt']/text()")[0]
    except:
        orientation = 'null'

    try:
        location = ''
        location_list = tree.xpath(
            "//div[@class='tr-line clearfix'][2]/div[@class='trl-item1 w182']/div[@class='tt']/text()|"
            "//div[@class='tab-cont-right']/div[@class='tr-line clearfix'][2]/div[@class='trl-item1 w182']/div[@class='tt']/a[@class='link_rk']/text() |"
            "//div[@class='tr-line clearfix'][2]/div[@class='trl-item1 w162']/div[@class='tt']/a[@class='link_rk']/text()"
        )
        for i in location_list:
            if '层' not in i:
                continue
            else:
                location = i.lstrip().rstrip()

        location_num = tree.xpath(
            "//div[@class='trl-item1 w162']/div[@class='font14']/text() | "
            "//div[@class='tr-line clearfix'][2]/div[@class='trl-item1 w182']/div[@class='font14']/text() |"
            "//div[@class='tr-line clearfix'][2]/div[@class='trl-item1 w182']/div[@class='font14']/text()")[0]

        floor = location + location_num
    except:
        floor = 'null'

    try:
        decoration = tree.xpath(
            "//div[@class='trl-item1 w00']/div[@class='tt']/a[@class='link_rk']/text() | "
            "//div[@class='trl-item1 w132']/div[@class='tt']/a[@class='link_rk']/text()")[0]
    except:
        decoration = 'null'

    try:
        housingEstate = tree.xpath("//a[@id='agantzfxq_C02_07']/text()")[0]
    except:
        housingEstate = 'null'

    try:
        address = tree.xpath("//div[@class='trl-item2 clearfix'][2]/div[@class='rcont']/a/text()")[0]
    except:
        address = 'null'

    try:
        brightSpot = tree.xpath("//li[@class='font14 fyld']/div[@class='fyms_con floatl gray3']/text()")[0]
    except:
        brightSpot = 'null'

    try:
        housingEstateIntroduction = tree.xpath(
            "//li[@class='font14 xqjs']/div[@class='fyms_con floatl gray3']/text()")[0]
    except:
        housingEstateIntroduction = 'null'

    houseDetail = HouseDetail(tittle, price, payment, leasingMode, houseType, area, orientation, floor, decoration,
                              housingEstate, address, brightSpot, housingEstateIntroduction)

    return houseDetail


# 写入本地文件
def writeToLocal(filePath, str) -> None:
    with open(file=filePath, mode='a', encoding='UTF-8') as file:
        file.write(str)


# 创建数据表
def createTable(table) -> None:
    database = pymysql.connect(
        host=ConnectMysql.host,
        user=ConnectMysql.user,
        password=ConnectMysql.password,
        database="house"
    )
    cursor = database.cursor()
    cursor.execute("DROP TABLE IF EXISTS {}".format(table))
    sql = """
            create table {}
            (
                id                        int auto_increment primary key,
                tittle                    varchar(150) null comment '标题',
                price                     int          null comment '价格 ？/月',
                payment                   varchar(30)  null comment '支付方式：季付、年付',
                leasingMode               varchar(30)  null comment '出租方式：整租、合租',
                houseType                 varchar(30)  null comment '户型：2室1厅',
                area                      varchar(30)  null comment '面积',
                orientation               varchar(30)  null comment '朝向',
                floor                     varchar(30)  null comment '楼层',
                decoration                varchar(30)  null comment '装修',
                housingEstate             varchar(60)  null comment '小区',
                address                   varchar(120) null comment '地址',
                brightSpot                text         null comment '亮点',
                housingEstateIntroduction text         null comment '小区介绍'
            );
        """.format(table)
    cursor.execute(sql)
    database.close()


# 写入MySQL数据库（house数据库需要提前创建）
def saveToMysql(houseDetail: HouseDetail, table: str) -> None:
    database = pymysql.connect(
        host=ConnectMysql.host,
        user=ConnectMysql.user,
        password=ConnectMysql.password,
        database="house"
    )
    cursor = database.cursor()
    # 插入数据
    insertSql = """
        INSERT INTO {}(tittle,price,payment,leasingMode,houseType,area,orientation,floor,decoration,housingEstate,address,brightSpot,housingEstateIntroduction) 
        VALUES('{}', {}, '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}')
    """.format(table,
               houseDetail.__dict__.get('_HouseDetail__tittle'),
               houseDetail.__dict__.get('_HouseDetail__price'),
               houseDetail.__dict__.get('_HouseDetail__payment'),
               houseDetail.__dict__.get('_HouseDetail__leasingMode'),
               houseDetail.__dict__.get('_HouseDetail__houseType'),
               houseDetail.__dict__.get('_HouseDetail__area'),
               houseDetail.__dict__.get('_HouseDetail__orientation'),
               houseDetail.__dict__.get('_HouseDetail__floor'),
               houseDetail.__dict__.get('_HouseDetail__decoration'),
               houseDetail.__dict__.get('_HouseDetail__housingEstate'),
               houseDetail.__dict__.get('_HouseDetail__address'),
               houseDetail.__dict__.get('_HouseDetail__brightSpot'),
               houseDetail.__dict__.get('_HouseDetail__housingEstateIntroduction'),
               )
    try:
        cursor.execute(insertSql)
        database.commit()
    except:
        print(insertSql)
        database.rollback()
        print("插入失败------>")
        print(houseDetail.__dict__)

    database.close()


# 采集函数
def mainFun(urlFormat: str, city: str) -> None:
    # url = 'https://taiyuan.zu.fang.com/house/i3{}/?rfss=1-af4a20932a109ebb47-5c'
    url = urlFormat

    createTable(city)

    for pageNum in range(1, 101):
        onePageHrefList = getOnePageHouseList(url.format(pageNum))
        # 打印第几页
        print(str(pageNum) + '============================>')
        # 打印获取到的详情页列表
        print(onePageHrefList)

        # 异常处理：当访问不到时从新刷新验证码，并重新访问
        if len(onePageHrefList) == 0:
            print("先验证，再回车继续。")
            print(url.format(pageNum))
            os.system("pause")
            onePageHrefList = getOnePageHouseList(url.format(pageNum))

        for onePageUrl in onePageHrefList:
            houseDetail = getOneHouseDetail(onePageUrl)
            # 异常处理：当访问不到时从新刷新验证码，并重新访问
            if (houseDetail.__dict__.get('_HouseDetail__tittle') == 'null' or
                    houseDetail.__dict__.get('_HouseDetail__price') == 'null' or
                    houseDetail.__dict__.get('_HouseDetail__payment') == 'null'):
                print("先验证，再回车继续。")
                print(onePageUrl)
                os.system("pause")
                houseDetail = getOneHouseDetail(onePageUrl)

            print(onePageUrl)
            print(houseDetail.__dict__)
            writeToLocal(city + ".txt", str(houseDetail.__dict__) + '\n')
            saveToMysql(houseDetail=houseDetail, table=city)


if __name__ == '__main__':
    url = 'https://taiyuan.zu.fang.com/house/i3{}/?rfss=1-af4a20932a109ebb47-5c'
    mainFun(urlFormat=url, city="taiyuan")
